AntApache ConfigurationAppleScriptCC#C++Cold FusionDelphi PascalFlash ActionScriptHTML / CSSJSP / JSTL / JSFJavaJavascript / AjaxObjective-C CocoaPHPPerlPythonRuby / RailsSQLUnix / ShellXML-SchemaXSLT / XPATHDirector LingoLego MindstormsObjective-C SwarmPIC assembly   Whole Words   Literal Phrase       Examples

Source code below from: Reality ColdFusion: Intranets and Content Management By Ben Forta Published 25 September, 2002 Average rating       Powells     Alibris

<!---
    reports.cfc
    
    * Reports component
    * Note that this is a display component
    * Provides a range of chart based reports for expense claims and for timesheets
    
    February 2002; Andi Hindle
    cfcm@ampersand-e.com
--->


<cfcomponent displayName="reports" hint="Returns graphs or charts to present reports">

<!--- REPORTING FOR TIME SHEETS --->

<cffunction name="showTSWeekly" displayName="showTSWeekly" hint="Displays a chart showing weekly activity breakdown" access="public" output="true">

    <cfargument name="startDate" type="date" required="yes" default="#request.getWeekStart(request.stApp.date)#" displayname="startDate" hint="Start date for the report">
    <cfargument name="endDate" type="date" required="yes" default="#request.nextWeek(arguments.startDate)#" displayname="endDate" hint="End date for the report:allows you to report on more than one week">
    <cfscript>
        thisObj = createObject("component","cfcm.system.timeSheet");
        qTS = thisObj.getReport(arguments.startDate,arguments.endDate);
    </cfscript>

    <h2>Week overview report</h2>
    <cfif qTS.recordCount NEQ 0>
    <p>Showing the weekly report for week commencing #dateFormat(arguments.startDate,"dddd dd mmmm yyyy")#
    <br>To show detailed information on each activity type, simply click on that segment in the pie chart.</p>

    
    <cfchart format="flash" chartheight="200" chartwidth="425" showborder="yes" url="reports.cfm?report=showTSDrillDown&activity=$itemLabel$&startDate=#arguments.startDate#">
        <cfchartseries type="pie" query="qTS" itemcolumn="activityName" valuecolumn="duration">
    </cfchart>

    <p><cfoutput><a href="buildExcel.cfm?userID=#getAuthUser()#&startDate=#arguments.startDate#&endDate=#arguments.endDate#">Build hardcopy of data in Excel format.</a></cfoutput></p>

    <cfelse>
        <p>There were no timesheets filed for week commencing #dateFormat(arguments.startDate,"dddd dd mmmm yyyy")#</p>
    </cfif>
    
    <p>
    [
    <a href="reports.cfm?startDate=#request.lastWeek(arguments.startDate)#">See an earlier week</a>
    <cfif arguments.startDate NEQ request.getWeekStart(request.stApp.date)>
    |
    <a href="reports.cfm?startDate=#request.nextWeek(arguments.startDate)#">See a later week</a>
    </cfif>
    ]
    </p>
</cffunction>

<cffunction name="showTSDrillDown">
    <cfargument name="activity" required="yes">
    <cfargument name="startDate" required="yes">

    <!---
        get the activity ID for the drilldown query from the
        activity name I can pass through from the top-level chart itself
        Cached for performance
    --->
    <cfquery name="qID" datasource="#request.stApp.dsn#" cachedwithin="#request.stApp.longCache#">
    SELECT id FROM activities
    WHERE activityName = '#arguments.activity#'
    </cfquery>

    <!--- now get the drilldown data for display.  Cached for performance --->
    
    <cfquery name="qTS" datasource="#request.stApp.dsn#">
    SELECT timesheets.id, timesheets.activityID, timesheets.userID, timesheets.date, timesheets.duration, timesheets.notes, activities.activityName
    FROM timesheets, activities
    WHERE timesheets.userID='#getAuthUser()#'
    AND timesheets.activityID = #qID.id#
    AND activities.id=timesheets.activityID
    AND date BETWEEN #arguments.startDate# AND #request.nextWeek(arguments.startDate)#
    </cfquery>

    <h2>Drill down analysis</h2>

    <p><cfoutput><a href="reports.cfm?report=showTSWeekly&startDate=#arguments.startDate#">Return to the main chart...</a></cfoutput></p>
    <cfchart format="flash" showborder="yes" chartheight="200" chartwidth="425">
        <cfchartSeries query="qTS" type="pie"  valuecolumn="duration" itemcolumn="notes">
    </cfchart>

</cffunction>

<cffunction name="showTSAnnual">

    <cfscript>
        thisObj = createObject("component","cfcm.system.timeSheet");
        qTS = thisObj.getReport(dateAdd('m',-12,request.getWeekStart(request.stApp.date)),request.getWeekStart(request.stApp.date));
    </cfscript>

    <h2>Annual overview report</h2>
    <cfif qTS.recordCount NEQ 0>

    <cfchart format="flash" chartheight="200" chartwidth="425" showborder="yes">
        <cfchartseries type="pie" query="qTS" itemcolumn="activityName" valuecolumn="duration">
    </cfchart>
    <cfelse>
        <p>There are no timesheet entries for the year.</p>
    </cfif>
    
    <p>
    [
    <a href="reports.cfm">Return to main report page</a>
    ]
    </p>

</cffunction>


<cffunction name="showTSEmp" roles="peasant,lord">
    <cfargument name="startdate" required="yes" type="date" default="#dateAdd('d',-300,request.stApp.date)#">
    <cfargument name="endDate" required="yes" type="date" default="#request.stApp.date#">
    <!--- get data for all staff who report to this manager --->
    <cfscript>
        // create time sheet and user instances
        thisTS = createObject("component","cfcm.system.timeSheet");
        thisUser = createObject("component","cfcm.system.user");
        // and an array to hold the reports in
        aTSReports = arrayNew(1);
        // get a list of reports for this manager
        qReports = thisUser.getReports(getAuthUser());
        // loop through the reports get their data
        for(i=0;i LT qReports.recordCount;i=i+1) {
            // get the report for this user and place into the array
            aTSReports[i+1] = thisTS.getReport(userID=qReports.id[i+1],startDate=arguments.startDate,endDate=arguments.endDate);
        }
    </cfscript>

<h2>Annual group report</h2>

<p>Annual time usage for your direct reports.</p>

<cfchart chartheight="300" chartwidth="425" showborder="yes">
<!--- loop over the array to get the queries out --->
    <cfloop index="i" from="1" to="#arrayLen(aTSReports)#" step="1">
<!--- extract the query and place in a local static query var --->
    <cfset thisQuery="#aTSReports[i]#">
    <cfchartSeries serieslabel="#thisUser.getFullName(thisQuery.userID)#" query="thisQuery" type="bar" itemcolumn="activityName" valuecolumn="duration">        
</cfloop>
</cfchart>      

    <p>
    [
    <a href="reports.cfm">Return to main report page</a>
    ]
    </p>
    
</cffunction>

<!--- REPORTING FOR EXPENSE CLAIMS --->

<cffunction name="showExpBreakdown" access="public" output="yes" hint="Graph breakdown of expense spending for each user.">
    <cfargument name="userID" required="yes" type="uuid" default="#getAuthUser()#" hint="Require user ID.  Default is this user.">
    <cfargument name="startDate" required="yes" type="date" default="#dateAdd('ww',-4,request.getWeekStart(request.stApp.date))#" hint="Require start date.  Default is one month prior to this week start.">
    <cfargument name="endDate" required="yes" type="date" default="#dateAdd('d',7,request.getWeekStart(request.stApp.date))#" hint="Require end date.  Default is the end of this current week.">

    <!--- get the data --->
    <cfscript>
        // get a claim component to work with
        thisClaim=createObject('component','cfcm.system.claim');
        // get the data list for the timeframe in question
        qDetails=thisClaim.report(arguments.userID,'list',arguments.startDate,arguments.endDate);
        // and the claim types
        qClaimTypes=thisClaim.getClaimTypes();
    </cfscript>

    <!--- perftweak: only bother to process if there's data here --->
    <cfif qDetails.recordCount NEQ 0>
    
    <!--- make a new query object --->
        <cfset qFinalData=queryNew('claimType,gbpValue')>

    <!--- for each claim type...--->
        <cfloop query="qClaimTypes">
            <!--- get the summary data... --->
            <cfquery name="qSummaryData" dbtype="query">
                SELECT SUM(gbpAmmount) AS sumData
                FROM qDetails
                WHERE qDetails.claimType='#qClaimTypes.claimType#'
            </cfquery>
            <!--- and add it to my new query --->
            <cfscript>
                queryAddRow(qFinalData);
                querySetCell(qFinalData,'claimType',qClaimTypes.claimType);
                querySetCell(qFinalData,'gbpValue',qSummaryData.sumData);
            </cfscript>
        </cfloop>
    
    
    <p>The following graph shows a breakdown of your claims for the past month.<br>
    Float over a bar to get the precise value of that bar in GBP.<br>
    Use the drop down boxes below the graph to alter the time period.
    </p>

    
    <!--- now build the chart from qFinalData --->
            
    <cfchart format="flash" chartheight="200" chartwidth="300">
        <cfchartSeries query="qFinalData" type="bar" itemcolumn="claimType" valuecolumn="gbpValue">
    </cfchart>
    <!--- ... otherwise, let the user know --->
    <cfelse>
    <p>The default claims breakdown graph is not shown, because you have no expense claim data for this period.<br>
    But you may use the drop down boxes below to choose a different time period.</p>
    </cfif>
    
    <form method="post" action="#cgi.path_info#?#cgi.query_String#">
    <table>
        <tr>
            <th>Start Week<br><small><cfoutput>(#dateFormat(arguments.startDate,"dddd dd mmmm yyyy")#)</cfoutput></small></th>
            <th>End Week<br><small><cfoutput>(#dateFormat(arguments.endDate,"dddd dd mmmm yyyy")#)</cfoutput></small></th>
        </tr>

        <tr>
            <td>
                <select name="startDate">
                    <cfloop from="-2" to="2" index="i" step="1">
                        <cfset thisDate=dateAdd('ww',i,arguments.startDate)>
                        <cfoutput>
                            <option value="#thisDate#" <cfif i EQ 0>selected</cfif>>#dateFormat(thisDate,"dddd dd mmmm yyyy")#</option>
                        </cfoutput>
                    </cfloop>
                </select> 
            </td>
            <td>
                <select name="endDate">
                    <cfloop from="-2" to="2" index="i" step="1">
                        <cfset thisDate=dateAdd('ww',i,arguments.endDate)>
                        <cfoutput>
                            <option value="#thisDate#" <cfif i EQ 0>selected</cfif>>#dateFormat(thisDate,"dddd dd mmmm yyyy")#</option>
                        </cfoutput>
                    </cfloop>
                </select>
            </td>
        </tr>   
        <tr>
            <td colspan="2" align="right"><input type="submit" name="btnSubmit" value="Build new chart..."></td>
        </tr>
    </table>
    </form>
    

</cffunction>

<cffunction name="showExpMonthTotal" hint="Displays monthly expense totals for a fixed one-year period." access="public" roles="peasant,lord">
    <!--- first get the data --->
    <cfscript>
    // figure out what year we're in and set up the start and end dates accordingly
        thisYear=datePart('yyyy',request.stApp.date);
        startDate="01 January " & thisYear;
        endDate="31 December " & thisYear;
    // need a claim component to work with
        thisClaim=createObject("component","cfcm.system.claim");
    // and a user component
        thisUser=createObject("component","cfcm.system.user");
    // need a list of direct reports
        qUsers=thisUser.getReports(getAuthUser());
    // and a list of claimTypes
        qClaimTypes=thisClaim.getClaimTypes();
    // turn qUsers into a list....
        lUserIDs = "";
        for(i=1;i LTE qUsers.recordCount; i=i+1) {
        // format this ID correctly
            thisID=request.quoterize(qUsers.id[i]);
        // general processing
            if(i NEQ 1) {
                lUserIDs=lUserIDs & "," & thisID; 
            }
        //first time round
            else {
                lUserIDs=thisID;
            }
        }
    //... and pass into the claim component to get some raw data
    qDetails=thisClaim.report(lUserIDs,'list',startDate,endDate);

    // now build a new query to hold the final data in...
    qFinal=queryNew('month,gbpValue');
    
    // ...and extract summary values from the raw data.
    // for each month
    for(i=1;i LTE 12; i=i+1) {
        // add a new row to the query
        queryAddRow(qFinal);
        // set the month to the correct value
        querySetCell(qFinal,'month',monthAsString(i));
        // loop over the raw data
        thisSum=0;
        for(j=1; j LTE qDetails.recordCount; j=j+1) {
        // add relevant values to thisSum
            if(datePart('m',qDetails.submitDate[j]) EQ i){
                thisSum=thisSum + qDetails.gbpAmmount[j];
            }
        }
        // and set the cell
        querySetCell(qFinal,'gbpValue',thisSum);
    }

    </cfscript>

    <!--- now render everything --->
    <p>The following graph shows total expenses for your team for each month of the current year.<br>
    Float over a bar to get the precise value of that bar in GBP.<br>
    </p>
    
    <!--- now build the chart from qFinalData --->
            
    <cfchart format="flash" chartheight="200" chartwidth="300">
        <cfchartSeries query="qFinal" type="bar" itemcolumn="claimType" valuecolumn="gbpValue">
    </cfchart>

    <p><a href="report.cfm">Return</a> to the main report page?</p>

</cffunction>

<cffunction name="showExpCatTotal">
    <cfargument name="month" required="no" hint="Report month may be specified.  Takes precendence if defined.">
    <cfargument name="startDate" required="no" hint="Report start date may be specified.  Month overrides if defined. Defaults to thirty days prior to endDate if appropriate. Always moves to start of specified week, since claims are calculated on a weekly basis.">
    <cfargument name="endDate" required="no" hint="Report end date may be specified.  Defaults to thirty days after startDate if not defined. Always moves to start of specified week, since claims are calculated on a weekly basis.">
    
    <!--- first get the data --->
    <cfscript>
    // set up start and end dates if not already defined
    if(NOT isDefined('arguments.startDate') AND NOT isDefined('arguments.endDate')) {
    // if month is defined, month takes precedence
    if(isDefined('arguments.month')) {
            monthStart='01 ' & monthAsString(arguments.month) & " " & datePart('yyyy',request.stApp.date); 
            arguments.startDate=createODBCDate(monthStart);
            arguments.endDate=createODBCDate(dateAdd('d',daysInMonth(arguments.month)-1,arguments.startDate));
    }           
    
    // no month?
    else {
        // next most likely is a start date
        if(isDefined('arguments.startDate') AND NOT isDefined('arguments.endDate')) {
            arguments.endDate=createODBCDate(dateAdd('d',30,arguments.startDate));
        }
        // nope? OK... end date?    
        else {
            if(isDefined('arguments.endDate') AND NOT isDefined('arguments.startDate')) {
                arguments.startDate=createODBCDate(dateAdd('d',-30,arguments.endDate));
            }
            // gosh, you are making it tricky.  Fine.  Here's a default
            else {
                daysBack=datePart('d',request.stApp.date)-1;
                daysOn=daysInMonth(request.stApp.date)-daysBack-1;
                arguments.startDate=createODBCDate(dateAdd('d',-daysBack,request.stApp.date));
                arguments.endDate=createODBCDate(dateAdd('d',daysOn,request.stApp.date));           
            }
        }
    }
    }       

    // now make sure we're starting at the beginning of a week
    arguments.startDate=request.getWeekStart(arguments.startDate);
    arguments.endDate=request.getWeekStart(arguments.endDate);

    // need a claim component to work with
    thisClaim=createObject("component","cfcm.system.claim");
    // and a user component
    thisUser=createObject("component","cfcm.system.user");
    // need a list of direct reports
    qUsers=thisUser.getReports(getAuthUser());
    // and a list of claimTypes
    qClaimTypes=thisClaim.getClaimTypes();
    // turn qUsers into a list....
    lUserIDs = "";
    for(i=1;i LTE qUsers.recordCount; i=i+1) {
        // format this ID correctly
        thisID=request.quoterize(qUsers.id[i]);
        // general processing
        if(i NEQ 1) {
            lUserIDs=lUserIDs & "," & thisID; 
        }
        //first time round
        else {
            lUserIDs=thisID;
        }
    }
    //... and pass into the claim component to get some raw data
    qDetails=thisClaim.report(lUserIDs,'list',arguments.startDate,arguments.endDate);
    </cfscript>
    
    <!--- now get the summary data for each claim type --->
    <!--- perftweak: only bother to process if there's data here --->
    <cfif qDetails.recordCount NEQ 0>
    
    <!--- make a new query object --->
        <cfset qFinalData=queryNew('claimType,gbpValue')>

    <!--- for each claim type...--->
        <cfloop query="qClaimTypes">
            <!--- get the summary data... --->
            <cfquery name="qSummaryData" dbtype="query">
                SELECT SUM(gbpAmmount) AS sumData
                FROM qDetails
                WHERE qDetails.claimType='#qClaimTypes.claimType#'
            </cfquery>
            <!--- and add it to my new query --->
            <cfscript>
                queryAddRow(qFinalData);
                querySetCell(qFinalData,'claimType',qClaimTypes.claimType);
                querySetCell(qFinalData,'gbpValue',qSummaryData.sumData);
            </cfscript>
        </cfloop>

    <!--- now render everything --->
    <p>The following graph shows a total expenses for your team for each claim category.<br>
    Float over a bar to get the precise value of that bar in GBP.<br>
    Use the form below the graph to choose a different time period for analysis.
    </p>

    
    <!--- now build the chart from qFinalData --->
            
    <cfchart format="flash" chartheight="200" chartwidth="300">
        <cfchartSeries query="qFinalData" type="bar" itemcolumn="claimType" valuecolumn="gbpValue">
    </cfchart>
    <!--- ... otherwise, let the user know --->
    <cfelse>
    <p>The monthly total graph is not shown because there were no claims by your team during the period.<br>
    But you may use the form below to choose a different time period for analysis.</p>
    </cfif>
    
    <cfform method="post" action="#cgi.path_info#?#cgi.query_String#">
    <table>
        <tr>
            <th>Start Week<br><small><cfoutput>(#dateFormat(arguments.startDate,"dddd dd mmmm yyyy")#)</cfoutput></small></th>
            <th>End Week<br><small><cfoutput>(#dateFormat(arguments.endDate,"dddd dd mmmm yyyy")#)</cfoutput></small></th>
        </tr>

        <tr>
            <td>
                <select name="startDate">
                    <cfloop from="-2" to="2" index="i" step="1">
                        <cfset thisDate=dateAdd('ww',i,arguments.startDate)>
                        <cfoutput>
                            <option value="#thisDate#" <cfif i EQ 0>selected</cfif>>#dateFormat(thisDate,"dddd dd mmmm yyyy")#</option>
                        </cfoutput>
                    </cfloop>
                </select> 
            </td>
            <td>
                <select name="endDate">
                    <cfloop from="-2" to="2" index="i" step="1">
                        <cfset thisDate=dateAdd('ww',i,arguments.endDate)>
                        <cfoutput>
                            <option value="#thisDate#" <cfif i EQ 0>selected</cfif>>#dateFormat(thisDate,"dddd dd mmmm yyyy")#</option>
                        </cfoutput>
                    </cfloop>
                </select>
            </td>
        </tr>   
        <tr>
            <td colspan="2" align="right"><input type="submit" name="btnSubmit" value="Build new chart..."></td>
        </tr>
    </table>
    </cfform>   

</cffunction>

<cffunction name="showExpEmpTotal" hint="Shows yearly expense totals per employee." roles="lord,peasant" output="yes" access="public">
    <cfscript>
    // figure out what year we're in and set up the start and end dates accordingly
        thisYear=datePart('yyyy',request.stApp.date);
        startDate="01 January " & thisYear;
        endDate="31 December " & thisYear;
    // need a claim component to work with
        thisClaim=createObject("component","cfcm.system.claim");
    // and a user component
        thisUser=createObject("component","cfcm.system.user");
    // need a list of direct reports
        qUsers=thisUser.getReports(getAuthUser());
    // and a list of claimTypes
        qClaimTypes=thisClaim.getClaimTypes();
    // turn qUsers into a list....
        lUserIDs = "";
        for(i=1;i LTE qUsers.recordCount; i=i+1) {
        // format this ID correctly
            thisID=request.quoterize(qUsers.id[i]);
        // general processing
            if(i NEQ 1) {
                lUserIDs=lUserIDs & "," & thisID; 
            }
        //first time round
            else {
                lUserIDs=thisID;
            }
        }
    //... and pass into the claim component to get some raw data
    qDetails=thisClaim.report(lUserIDs,'list',startDate,endDate);
    
    // make a new query to hold final data
    qFinal=queryNew('employee,gbpValue');
    </cfscript>

    <!--- for each user --->
    <cfloop from="1" to="#listLen(lUserIDs)#" step="1" index="i">
        <!--- get this user ID --->
        <cfset thisUserID=mid(listGetAt(lUserIDs,i),2,35)>
        <!--- add a row --->
        <cfset bAdd=queryAddRow(qFinal)>
        <!--- add the username to the new row --->
        <cfset bSetName=querySetCell(qFinal,'employee',thisUser.getFullName(thisUserID))>
        <!--- get the summary data for this user --->
        <cfquery name="qThisSummaryData" dbtype="query">
        SELECT SUM(gbpAmmount) AS gbpAmmount
        FROM qDetails
        WHERE userID='#thisUserID#'
        </cfquery>      
        <!--- and add this data to the results --->
        <cfset bSetValue=querySetCell(qFinal,'gbpValue', qThisSummaryData.gbpAmmount)>
    </cfloop>

    <!--- now render everything --->
    <p>The following graph shows total expenses for each member of your team for the current year.<br>
    Float over a bar to get the precise value of that bar in GBP.<br>
    </p>
    
    <!--- now build the chart from qFinalData --->
            
    <cfchart format="flash" chartheight="200" chartwidth="300">
        <cfchartSeries query="qFinal" type="bar" itemcolumn="employee" valuecolumn="gbpValue">
    </cfchart>

    <p><a href="report.cfm">Return</a> to the main report page?</p>



</cffunction>


</cfcomponent>